package com.xyzboom.database.dao;

import com.xyzboom.beans.CourseDeleteResult;
import com.xyzboom.database.entity.Course;
import com.xyzboom.database.utils.DatabaseUtils;
import com.xyzboom.database.utils.SQLLimit;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.AbstractListHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class CourseDAO {
    public static boolean addNewCourse(Course course) throws SQLException {
        return DatabaseUtils.getQueryRunner()
                .update("INSERT INTO course (cId, cName, cNum, cType)\n" +
                                "VALUES (?, ?, ?, ?);", course.getId(), course.getName(),
                        course.getNum(), course.getType()) != 0;
    }

    public static Integer getAllCourseCount() {
        try {
            return DatabaseUtils.getQueryRunner().query(
                    "select count(cId) as ? " +
                            "from course " +
                            "where true;",
                    CommonDAOUtil.getCountHandler(),
                    CommonDAOUtil.COUNT_COLUMN_LABEL
            );
        } catch (SQLException e) {
            return null;
        }
    }

    public static List<Course> getAllCourses() throws SQLException {
        return DatabaseUtils.getQueryRunner().query("select * from course", getListHandler());
    }

    public static List<Course> getAllCourses(SQLLimit limit) {
        return getAllCourses(Course.CourseColumn.NULL, false, limit);
    }

    public static List<Course> getAllCourses(Course.CourseColumn oderBy) {
        return getAllCourses(oderBy, false, null);
    }

    public static List<Course> getAllCourses(Course.CourseColumn oderBy,
                                             boolean descend) {
        return getAllCourses(oderBy, descend, null);
    }

    public static List<Course> getAllCourses(Course.CourseColumn oderBy,
                                             boolean descend, SQLLimit limit) {
        StringBuilder sb = new StringBuilder("select * from course");
        if (oderBy != Course.CourseColumn.NULL) {
            sb.append(" order by ");
            sb.append(oderBy.value);
            sb.append(" ");
        }
        if (descend) {
            sb.append(" desc");
        }
        if (limit != null) {
            sb.append(limit.getSqlStr());
        }
        try {
            return DatabaseUtils.getQueryRunner().query(sb.toString(), getListHandler());
        } catch (SQLException e) {
            return null;
        }
    }

    /**
     * 从数据库中删除对应id的course
     *
     * @param id 需要删除的id
     * @return 是否成功
     */
    public static boolean deleteCourse(int id) {
        try {
            return DatabaseUtils.getQueryRunner().update("DELETE FROM course WHERE cId=?;", id) != 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }


    public static CourseDeleteResult deleteCourse(List<Integer> ids) {
        CourseDeleteResult result = new CourseDeleteResult();
        QueryRunner runner = DatabaseUtils.getQueryRunner();
        try {
            for (Integer id : ids) {
                if (id == null) {
                    continue;
                }
                if (runner.update("DELETE FROM course WHERE cId=?;", id) == 0) {
                    result.getFailIds().add(id);
                } else {
                    result.getSuccessIds().add(id);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            result.getFailIds().addAll(ids);
        }
        return result;
    }

    public static ResultSetHandler<Course> getHandler() {
        return rs -> {
            if (!rs.next()) {
                return null;
            }
            return new Course(
                    rs.getInt("cId"),
                    rs.getString("cName"),
                    rs.getInt("cNum"),
                    rs.getString("cType")
            );
        };
    }

    public static AbstractListHandler<Course> getListHandler() {
        return new AbstractListHandler<Course>() {
            @Override
            protected Course handleRow(ResultSet rs) throws SQLException {
                return new Course(
                        rs.getInt("cId"),
                        rs.getString("cName"),
                        rs.getInt("cNum"),
                        rs.getString("cType")
                );
            }
        };
    }

    public static List<Course> getAllCourses(Enum<?> orderBy, boolean descend, SQLLimit limit) {
        return getAllCourses((Course.CourseColumn) orderBy, descend, limit);
    }
}
